﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Audit_Configuration_Oracle.Model;
using Oracle.DataAccess.Client;

namespace Audit_Configuration_Oracle.DAL
{
    public class NewRuleDAL
    {
        //ConnectionString cn = new ConnectionString(user, pass, "XE");

        public static List<string> GetUserList()
        {
            List<string> users = null;
            DBConnectionSing db = null;
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = db.conn;

                cmd.CommandText = "SELECT USERNAME FROM DBA_USERS ORDER BY USERNAME";

                using (OracleDataReader rd = cmd.ExecuteReader())
                {
                    users = new List<string>();
                    while (rd.Read())
                    {
                        users.Add(rd.GetString(0));
                    }
                }
                cmd = null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
                
            }


            return users;
        }
      
        public static List<string> GetTableList()
        {
            List<string> users = null;
            DBConnectionSing db = null;
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = db.conn;

                cmd.CommandText = "SELECT TABLE_NAME, OWNER FROM DBA_TABLES ORDER BY OWNER";

                using (OracleDataReader rd = cmd.ExecuteReader())
                {
                    users = new List<string>();
                    while (rd.Read())
                    {
                        users.Add(string.Format("{1}.{0}",rd["TABLE_NAME"],rd["OWNER"]));
                    }
                }
                cmd = null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;

            }


            return users;
        }

        public static bool SaveNewRule(string rule, List<string> operations, string target, string condition1, string condition2)
        {
            DBConnectionSing db = null;
            bool result = false;
            string newRule = "";
            switch (rule) 
            {
                case "S": 
                    {
                        newRule = SentenceRule(ref newRule,operations, target, condition1, condition2);
                        break;
                    }
                case "O":
                    {
                        newRule = ObjectRule(ref newRule, operations, target, condition1, condition2);
                        break;
                    }
                default: 
                    {
                        break; 
                    }
            }
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = db.conn;

                cmd.CommandText = newRule;

                cmd.ExecuteNonQuery();
                cmd = null;

                result = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            return result;
        }
       
        private static string SentenceRule(ref string newRule, List<string> operations, string target, string condition1, string condition2)
        {
            string key = "";
            string operationSeccion = "";
            for (int i = 0; i < operations.Count; i++)
            {
                if (i == 0)
                {
                    operationSeccion = string.Format("{0} TABLE", operations[i]);
                }
                else
                {
                    operationSeccion = string.Format("{0}, {1} TABLE", operationSeccion, operations[i]);
                }
            }
            if (!condition1.Equals(""))
            {
                key = "BY";
            }
            newRule = string.Format("AUDIT {0} BY {1} {2} {3} {4}", operationSeccion, target, key, condition1, condition2);
            return newRule;
        }

        private static string ObjectRule(ref string newRule, List<string> operations, string target, string condition1, string condition2)
        {
            string key = "";
            string operationSeccion = "";
            for (int i = 0; i < operations.Count; i++)
            {
                if (i == 0)
                {
                    operationSeccion = operations[i];
                }
                else
                {
                    operationSeccion = string.Format("{0}, {1}", operationSeccion, operations[i]);
                }
            }
            if (!condition1.Equals("")) 
            {
                key = "BY";
            }
            newRule = string.Format("AUDIT {0} ON {1} {2} {3} {4}", operationSeccion, target, key, condition1, condition2);
            return newRule;
        }

        public static bool SaveNewRulePrivilege(string rule, List<string> operations, string target, string condition1, string condition2)
        {
            DBConnectionSing db = null;
            bool result = false;
            List<string> newRule = new List<string>();
            PrivilegeRule(ref newRule,operations, target, condition1, condition2);
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = db.conn;
                for (int i = 0; i < newRule.Count; i++)
                {
                    cmd.CommandText = newRule[i];

                    cmd.ExecuteNonQuery();

                }
                
                cmd = null;
                result = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            return result;
        }
       
        private static void PrivilegeRule(ref List<string> newRule, List<string> operations, string target, string condition1, string condition2)
        {
            string key = "";
            string conditionaux = "";
            if (!condition1.Equals(""))
            {
                key = "BY";
            }
            for (int i = 0; i < operations.Count; i++)
            {
                if (operations[i].Equals("EXECUTE PROCEDURE"))
                {
                    conditionaux = condition1;
                }
                else
                {
                    if (!condition1.Equals(""))
                    {
                        conditionaux = "ACCESS";
                    }
                }
                newRule.Add(string.Format("AUDIT {0} BY {1} {2} {3} {4}", operations[i], target, key, conditionaux, condition2));
                conditionaux = "";
            }
        }
    }
}
